import 'dart:io';

import 'package:meeting_note/common.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class FileEntity {
  int? id;
  String? userName;
  String? fileName;
  String? path;
  String? status;
  String? errMsg;
  String? textContent;
  String? summary;
  int? createdAt;
  int? updatedAt;
  int? taskId;

  FileEntity({
    this.id,
    this.userName,
    this.fileName,
    this.path,
    this.status,
    this.errMsg,
    this.textContent,
    this.summary,
    this.createdAt,
    this.updatedAt,
    this.taskId,
  }) {
    status ??= FileStatus.pending;
    errMsg ??= '';
  }

  FileEntity copyWith({
    int? id,
    String? userName,
    String? fileName,
    String? path,
    String? status,
    String? errMsg,
    String? textContent,
    String? summary,
    int? createdAt,
    int? updatedAt,
    int? taskId,
  }) {
    return FileEntity(
      id: id ?? this.id,
      userName: userName ?? this.userName,
      fileName: fileName ?? this.fileName,
      path: path ?? this.path,
      status: status ?? this.status,
      errMsg: errMsg ?? this.errMsg,
      textContent: textContent ?? this.textContent,
      summary: summary ?? this.summary,
      createdAt: createdAt ?? this.createdAt,
      updatedAt: updatedAt ?? this.updatedAt,
      taskId: taskId ?? this.taskId,
    );
  }
}

class TextContentEntity {
  String startTime;
  String endTime;
  String content;

  TextContentEntity({
    required this.startTime,
    required this.endTime,
    required this.content,
  });
}

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  DatabaseHelper._internal();

  factory DatabaseHelper() => _instance;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'meeting_note.db');
    return await openDatabase(path, version: 1, onCreate: _onCreate);
  }

  Future<void> resetDatabase() async {
    final db = await database;
    await db.close();
    final path = join(await getDatabasesPath(), 'meeting_note.db');
    final file = File(path);
    if (await file.exists()) {
      await file.delete();
    }
    _database = null;
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE tokens (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        token TEXT NOT NULL,
        user_name TEXT,
        created_at INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_name TEXT,
        file_name TEXT,
        path TEXT NOT NULL,
        status TEXT,
        err_msg TEXT,
        text_content TEXT,
        summary TEXT,
        created_at INTEGER,
        updated_at INTEGER,
        task_id INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE chat_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_id INTEGER,
        role TEXT,
        content TEXT,
        created_at INTEGER,
        FOREIGN KEY (file_id) REFERENCES files (id)
      )
    ''');

    await db.execute('''
      CREATE TABLE messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        conversation_id INTEGER,
        user_id TEXT,
        user_name TEXT,
        role TEXT,
        content TEXT,
        order_id TEXT,
        reasoning_content TEXT,
        created_at INTEGER,
        updated_at INTEGER
      )
    ''');
    await db.execute(
      'CREATE INDEX idx_messages_conversation_created_at ON messages (conversation_id, created_at DESC)',
    );
    await db.execute('''
      CREATE TABLE conversation (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        user_id TEXT,
        user_name TEXT,
        created_at INTEGER,
        updated_at INTEGER
      )
    ''');
    await db.execute(
      'CREATE INDEX idx_conversation_created_at ON conversation (created_at DESC)',
    );

    // 如需按用户/会话分组查询最近N条，可加联合索引
    // await db.execute('CREATE INDEX idx_messages_user_order_created ON messages (user_id, order_id, created_at DESC)');
  }

  //FIXME: 当前是假token，后续需要替换为真实的token
  // Token operations
  Future<int> insertToken(String token, String userName) async {
    final db = await database;
    return await db.insert('tokens', {
      'token': token,
      'user_name': userName,
      'created_at': DateTime.now().millisecondsSinceEpoch,
    });
  }

  Future<String?> getLatestToken() async {
    final db = await database;
    final result = await db.query(
      'tokens',
      orderBy: 'created_at DESC',
      limit: 1,
    );
    if (result.isEmpty) return null;

    final token = result.first['token'] as String;
    final createdAt = result.first['created_at'] as int;
    final tokenAge = DateTime.now().difference(
      DateTime.fromMillisecondsSinceEpoch(createdAt),
    );

    // Token有效期7天
    return tokenAge.inDays < 7 ? token : null;
  }

  Future<String?> getTokenUser() async {
    final db = await database;
    final result = await db.query(
      'tokens',
      orderBy: 'created_at DESC',
      limit: 1,
    );
    return result.isNotEmpty ? result.first['user_name'] as String? : null;
  }

  // File operations
  Future<int> insertFile(FileEntity entity) async {
    final db = await database;
    return await db.insert('files', {
      'user_name': entity.userName,
      'file_name': entity.fileName,
      'path': entity.path,
      'status': entity.status,
      "err_msg": entity.errMsg,
      'text_content': entity.textContent,
      'summary': entity.summary,
      'created_at': DateTime.now().millisecondsSinceEpoch,
      'updated_at': DateTime.now().millisecondsSinceEpoch,
      'task_id': entity.taskId,
    });
  }

  Future<FileEntity?> getFileById(int id) async {
    final db = await database;
    final result = await db.query('files', where: 'id = ?', whereArgs: [id]);
    if (result.isEmpty) return null;

    final row = result.first;
    return FileEntity(
      id: row['id'] as int?,
      userName: row['user_name'] as String?,
      fileName: row['file_name'] as String?,
      path: row['path'] as String?,
      status: row['status'] as String?,
      errMsg: row['err_msg'] as String?,
      textContent: row['text_content'] as String?,
      summary: row['summary'] as String?,
      createdAt: row['created_at'] as int?,
      updatedAt: row['updated_at'] as int?,
      taskId: row['task_id'] as int?,
    );
  }

  Future<int> updateFileStatus(
    int id,
    String status,
    String errMsg, {
    int? taskId,
  }) async {
    final db = await database;
    final Map<String, dynamic> values = {
      'status': status,
      'err_msg': errMsg,
      'updated_at': DateTime.now().millisecondsSinceEpoch,
    };
    if (taskId != null) {
      values['task_id'] = taskId;
    }
    return await db.update('files', values, where: 'id = ?', whereArgs: [id]);
  }

  Future<int> updateFileTextContent(int id, String textContent) async {
    final db = await database;
    return await db.update(
      'files',
      {
        'text_content': textContent,
        'status': FileStatus.success,
        'err_msg': '',
      },
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<int> updateFileContent(int id, String text, String summary) async {
    final db = await database;
    return await db.update(
      'files',
      {'text_content': text, 'summary': summary},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<List<FileEntity>> getAllFiles(int limit, int offset) async {
    final db = await database;
    final result = await db.query(
      'files',
      orderBy: 'created_at DESC',
      limit: limit,
      offset: offset,
    );
    return result.map((row) {
      return FileEntity(
        id: row['id'] as int?,
        userName: row['user_name'] as String?,
        fileName: row['file_name'] as String?,
        path: row['path'] as String?,
        status: row['status'] as String?,
        errMsg: row['err_msg'] as String?,
        textContent: row['text_content'] as String?,
        summary: row['summary'] as String?,
        createdAt: row['created_at'] as int?,
        updatedAt: row['updated_at'] as int?,
        taskId: row['task_id'] as int?,
      );
    }).toList();
  }

  // messages表的增删改查
  Future<int> insertMessage(Map<String, dynamic> message) async {
    final db = await database;
    return await db.insert('messages', message);
  }

  Future<int> deleteMessage(int id) async {
    final db = await database;
    return await db.delete('messages', where: 'id = ?', whereArgs: [id]);
  }

  Future<int> updateMessage(int id, Map<String, dynamic> values) async {
    final db = await database;
    return await db.update(
      'messages',
      values,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<Map<String, dynamic>?> getMessageById(int id) async {
    final db = await database;
    final result = await db.query('messages', where: 'id = ?', whereArgs: [id]);
    return result.isNotEmpty ? result.first : null;
  }

  Future<List<Map<String, dynamic>>> getMessagesByConversationId(
    int conversationId, {
    int limit = 20,
    int offset = 0,
  }) async {
    final db = await database;
    final result = await db.query(
      'messages',
      where: 'conversation_id = ?',
      whereArgs: [conversationId],
      orderBy: 'created_at DESC',
      limit: limit,
      offset: offset,
    );
    return result;
  }

  Future<List<Map<String, dynamic>>> getMessagesCountByConversationId(
    int conversationId,
  ) async {
    final db = await database;
    final result = await db.query(
      'messages',
      columns: ['COUNT(*) AS count'],
      where: 'conversation_id = ?',
      whereArgs: [conversationId],
    );
    return result;
  }

  // conversation表的增删改查
  Future<int> insertConversation(Map<String, dynamic> conversation) async {
    final db = await database;
    return await db.insert('conversation', conversation);
  }

  Future<int> deleteConversation(int id) async {
    final db = await database;
    return await db.delete('conversation', where: 'id = ?', whereArgs: [id]);
  }

  Future<int> updateConversation(int id, Map<String, dynamic> values) async {
    final db = await database;
    return await db.update(
      'conversation',
      values,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<Map<String, dynamic>?> getConversationById(int id) async {
    final db = await database;
    final result = await db.query(
      'conversation',
      where: 'id = ?',
      whereArgs: [id],
    );
    return result.isNotEmpty ? result.first : null;
  }

  Future<List<Map<String, dynamic>>> getConversations({
    int limit = 20,
    int offset = 0,
  }) async {
    final db = await database;
    final result = await db.query(
      'conversation',
      orderBy: 'created_at DESC',
      limit: limit,
      offset: offset,
    );
    return result;
  }

  Future<int> getConversationCount() async {
    final db = await database;
    final result = await db.query(
      'conversation',
      columns: ['COUNT(*) AS count'],
    );
    return Sqflite.firstIntValue(result) ?? 0;
  }

  Future<Map<String, dynamic>?> getLatestConversation() async {
    final db = await database;
    final result = await db.query(
      'conversation',
      orderBy: 'created_at DESC',
      limit: 1,
    );
    return result.isNotEmpty ? result.first : null;
  }
}
